package com.csii.adhoc.util.excel;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @description:
 * @author: panChuanJiang
 * @create: 2021-09-13 16:39
 */
public class POIUtils {

    /***
     * 获取行的样式
     * @param row 行对象
     * @return 样式
     */
    public static List<CellStyle> getRowCellStyles(Row row) {
        List<CellStyle> cellStyles = new ArrayList<>(row.getLastCellNum());
        CellStyle cellStyle = null;
        for (int i = 0; i <= row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            if (cell == null) {//处理格式为空的情况
                cellStyles.add(cellStyle);
                continue;
            }
            cellStyle = cell.getCellStyle();
            cellStyles.add(cellStyle);
        }
        return cellStyles;
    }

    /**
     * 获取指定的行合并单元格信息
     *
     * @param sheet  sheet
     * @param row    行下标
     * @return 合并区域
     */
    public static List<MergedRegion> getRowMergedRegion(Sheet sheet, int row) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        List<MergedRegion> list = new ArrayList<>();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                MergedRegion mergedRegion = new MergedRegion();
                mergedRegion.setStartColumnIndex(firstColumn)
                        .setEndColumnIndex(lastColumn)
                        .setStartRowIndex(firstRow)
                        .setEndRowIndex(lastRow);
                list.add(mergedRegion);
            }
        }
        return list;
    }


    /**
     * 函数刷新
     */
    public static void refreshRow(Row row) {
        if (row != null) {
            for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                if (cell != null && cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    cell.setCellFormula(cell.getCellFormula());
                }
            }
        }
    }

    /**
     * 用于将任意格式的单元格内容读取为 String 类型
     *
     * @param cell poi cell 对象
     * @return 取出的 String
     */
    public static String getCellStringValue(Cell cell) {
        String cellValue = "";
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                cellValue = cell.getStringCellValue();
                if ("".equals(cellValue.trim()) || cellValue.trim().length() <= 0) {
                    cellValue = "";
                }
                break;
            case Cell.CELL_TYPE_NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                cell.setCellType(Cell.CELL_TYPE_FORMULA);
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_BLANK:
                cellValue = " ";
                break;
            default:
                break;
        }
        return cellValue;
    }

    /**
     * 打开一个 excel 文件，返回该 excel 的内存对象 workbook
     *
     * @param excelPath excel模板文件路径
     * @return excel 的内存对象
     */
    public static Workbook openOtherExcel(String excelPath) throws Exception {
        Workbook workbook;
        // 获取Excel后缀名
        String fileType = excelPath.substring(excelPath.lastIndexOf(".") + 1);
        // 获取Excel文件
        File excelFile = new File(excelPath);
        if (!excelFile.exists()) {
            throw new Exception("指定的Excel文件不存在");
        }
        if ("xls".equalsIgnoreCase(fileType)) {
            try (FileInputStream inputStream = new FileInputStream(excelFile)) {
                workbook = new HSSFWorkbook(inputStream);
            }
        } else if ("xlsx".equalsIgnoreCase(fileType)) {
            try (FileInputStream inputStream = new FileInputStream(excelFile)) {
                //workbook = new XSSFWorkbook(inputStream); //poi 3.7
                workbook = WorkbookFactory.create(inputStream); //poi 3.9
            }
        } else {
            throw new Exception(fileType + " 后缀的文件，不被认为是合法的 excel 文件");
        }
        return workbook;
    }
}

